create or replace package body dpa_audit as
procedure truncate_tab(table_name IN VARCHAR2) is
begin
    execute immediate 'truncate table '||table_name;
end truncate_tab;
procedure load_stage is
begin
    truncate_tab('tmp_audit_table');
    truncate_tab('tmp_audit_table_final');
        
    insert /*+ APPEND*/ into tmp_audit_table
        select 
            au.user_id, tab.table_name, column_name 
        from 
            dba_tab_cols col, dba_tables tab, dba_objects obj, audit_users au, audit_object_types at, audit_user_types aut
        where 
            AU.USER_ID = AUT.USTY_USER_ID and
            AUT.USTY_TYPE_ID = at.TYPE_id and
            tab.table_name not like '%$%' and 
            tab.table_name not like '%#%' and 
            tab.table_name = col.table_name and 
            tab.owner = AU.USER_NAME and
            obj.object_type = UPPER(AT.TYPE_NAME) and
            AUT.USTY_ENABLED = 'Y';
            
    commit; 
       
    insert /*+ APPEND*/ into tmp_audit_table_final
        select 
            t.user_id,
            T.TABELA,
            T.KOLUMNA,
            nvl2(f.audit_id,0,1), -- 1 is new value
            0 --not_to_delete
        from tmp_audit_table t, audit_table f
        where 
            t.user_id = f.user_id(+) and
            t.tabela = f.tabela(+) and
            T.KOLUMNA = f.kolumna (+);    
    
    commit; 
    
    --records_to_delete_in_final
    insert into tmp_audit_table_final
        select 
            f.user_id,
            f.TABELA,
            f.KOLUMNA,
            0, --new
            1--,nvl2(t.tabela,0,1) 
        from tmp_audit_table t, audit_table f
        where 
            t.user_id(+) = f.user_id and
            t.tabela(+) = f.tabela and
            T.KOLUMNA(+) = f.kolumna and
            nvl2(t.tabela,0,1) = 1;
            
    commit;
end;

procedure load_final is
begin
    merge into audit_table f
    using
    (
        select 
            user_id, 
            tabela,
            kolumna
        from
            tmp_audit_table_final
        where 
            is_to_delete = 0
        
    ) tmp
    on
    (
        tmp.user_id = f.user_id and
        tmp.tabela = f.tabela and 
        tmp.kolumna = f.kolumna
    )
    when not matched then 
        insert (audit_id, user_id, tabela, kolumna, change_date, add_date, actual_date) values (audit_seq.nextval,tmp.user_id,tmp.tabela,tmp.kolumna, sysdate, sysdate, sysdate)
    when matched then
        update set
        actual_date = sysdate,
        change_date = sysdate;
    
    update audit_table f set
        change_date = sysdate,
        delete_date = sysdate
    where exists
        (select 1 from tmp_audit_table_final t where 
            t.user_id = f.user_id and
            t.tabela = f.tabela and
            t.kolumna = f.kolumna and
            t.is_to_delete = 1
         );
    commit;    
end load_final;
end dpa_audit;
/